use SurveyMaster

/************************ Questions **************************/
go 
drop proc insertGridRow
go
create proc insertGridRow
@parentId bigint,
@text nvarchar(200),
@typeId tinyint,
@surveyId bigint
as
begin
declare @anyRowId bigint
select top 1 @anyRowId = id from questions where parentId = @parentId

if @anyRowId is null
begin
exec insertQuestion @parentId, @text, @typeId, @surveyId, 0
return
end

DECLARE @tblA TABLE([id] [bigint], [parentId] [bigint], [text] [nvarchar](200) ,
 [typeId] [tinyint] , [surveyId] [bigint] , [columnId] [bigint], mandatory bit);   
DECLARE @tblB TABLE([id] [bigint], [parentId] [bigint], [text] [nvarchar](200) ,
 [typeId] [tinyint] , [surveyId] [bigint] , [columnId] [bigint], mandatory bit); 

with nodes 
as (
 select q.*
 from questions q
 where q.id = @anyRowId
 union all
 select q.*
 from questions q
 inner join nodes n on n.id = q.parentid
)
INSERT INTO @tblA
select *
from nodes;

INSERT INTO questions
OUTPUT inserted.id, inserted.parentId,inserted.TEXT, inserted.typeId, inserted.surveyId, inserted.columnId, inserted.mandatory
INTO @tblB
SELECT ParentId, [text],typeId,surveyId,columnId, mandatory FROM  @tblA


UPDATE dbo.Questions
SET parentId=ids1.new
FROM dbo.Questions q1
INNER JOIN 
(
SELECT *
FROM dbo.Questions q
INNER JOIN 
(SELECT a.id AS old, b.id AS new FROM @tblA a
INNER JOIN @tblB b ON a.parentId=b.parentId AND a.[text]=b.[text] AND a.typeId=b.typeId AND a.surveyId = b.surveyId AND a.columnId = b.columnId)
ids ON q.parentId = ids.old
) ids1 on ids1.id=q1.id
WHERE q1.id IN (SELECT id FROM @tblB)

update Questions
set [text] = @text, typeId = @typeId, mandatory = 0
where id in (
SELECT b.id FROM @tblA a
INNER JOIN @tblB b ON a.parentId=b.parentId AND a.[text]=b.[text] AND a.typeId=b.typeId AND a.surveyId = b.surveyId AND a.columnId = b.columnId
where  a.id=@anyRowId)
end

go
drop proc getParentId
go
create proc getParentId
@id bigint
as
select parentId from Questions where id = @id

go
drop proc insertQuestion --157, 'a', 1, 1,0
go
create proc insertQuestion
@parentId bigint,
@text nvarchar(200),
@typeId tinyint,
@surveyId bigint,
@mandatory bit
as
begin
insert Questions(parentId, text, typeId, surveyId, mandatory) 
values(@parentId, @text, @typeId, @surveyId, @mandatory)
select @@identity
end

go
drop proc deleteByColumnId
go
create proc deleteByColumnId
@id bigint
as
begin
declare @sqlstr nvarchar(max)

set @sqlstr = ''

select  @sqlstr = @sqlstr + ' exec deleteQuestion ' + cast(q.id as nvarchar(max))
from Questions q where columnId = @id

exec (@sqlstr)
end

go
drop proc getGrandPaIdByColumnId --793
go
create proc getGrandPaIdByColumnId
@columnId bigint
as
begin
declare @anyRowId bigint
select top 1 @anyRowId = parentId from questions where columnId = @columnId
select parentId from questions where id = @anyRowId
end

go
drop proc insertCellQuestions
go
create proc insertCellQuestions
@columnId bigint,
@text nvarchar(200),
@typeId tinyint
as
begin
declare @firstColumnId bigint
declare @surveyId bigint
declare @firstCellId bigint

select @firstColumnId = id, @surveyId = surveyId 
from Questions 
where id = (select min(id) from Questions where columnId = @columnId)

insert Questions(parentId, text, typeId, surveyId) 
values(@firstColumnId, @text, @typeId, @surveyId)
select @firstCellId = @@identity

update questions set columnId = @firstCellId where id = @firstCellId

insert Questions(parentId, text, typeid, surveyId, columnId)
select id, @text, @typeId, surveyId, @firstCellId
from Questions 
where columnId = @columnId and id != @firstColumnId

end

go
drop proc insertColumnQuestions
go
create proc insertColumnQuestions
@parentId bigint,
@text nvarchar(200),
@typeId tinyint,
@mandatory bit
as
begin
declare @firstChildId bigint
declare @surveyId bigint
declare @firstGrandSonId bigint

select @firstChildId = id, @surveyId = surveyId 
from Questions 
where id = (select min(id) from Questions where parentId = @parentId)

insert Questions(parentId, text, typeId, surveyId, mandatory) 
values(@firstChildid, @text, @typeid, @surveyId, @mandatory)
select @firstGrandSonId = @@identity

update Questions set columnId = @firstGrandSonId where id = @firstGrandSonId

insert Questions(parentId, text, typeid, surveyId, columnId, mandatory)
select id, @text, @typeId, surveyId, @firstGrandSonId, @mandatory
from Questions 
where parentId = @parentId and id != @firstChildId
end


go
drop proc updateQuestion
go
create proc updateQuestion
@id bigint,
@text nvarchar(200),
@typeId tinyint,
@mandatory bit
as
update Questions 
set text = @text, typeId = @typeId, mandatory = @mandatory
where id = @id


go
drop proc getQuestionsByQuestionId
go
create proc getQuestionsByQuestionId
@id bigint
as
with nodes 
as (
	select q.*
	from questions q
	where q.id = @id

	union all

	select q.*
	from questions q
	inner join nodes n on n.id = q.parentid
)
select * from nodes

go
drop proc deleteQuestion 
go
create proc deleteQuestion 
@id bigint
as
with Nodes (id, parentId, [Level]) 
as (
    select  q.id, q.parentId, 0 AS [Level]
    from    Questions q
    where   q.Id = @id

    union all

    select  q.Id, q.parentId, n.[Level] + 1
    from    Questions q
        inner join Nodes n ON n.Id = q.parentId
)

delete
from Questions
where   id in (
    select  top 100 percent n.[Id] 
    from    Nodes n
    order by n.[Level] desc)


go
drop proc getQuestionsBySurveyId 
go
create proc getQuestionsBySurveyId 
@surveyId bigint
as
select q.*
from Questions q
where surveyId = @surveyId

go 
drop proc getQuestion
go
create proc getQuestion
@id bigint
as
select * from Questions where id = @id


/****************************** Surveys *******************************/
go

drop proc deleteSurvey
go
create proc deleteSurvey
@id bigint
as
begin try
begin tran
declare @sqlstr nvarchar(max)
set @sqlstr = ''

select  @sqlstr = @sqlstr + ' exec deleteQuestion ' + cast(q.id as nvarchar(max))
from Questions q where surveyId = @id and parentId is null

exec(@sqlstr)
delete from Surveys where @id = id

commit tran
end try
begin catch
if @@trancount > 0 rollback
    declare @errmsg nvarchar(4000), @errseverity int
    select @errmsg = error_message(), @errseverity = error_severity()
    raiserror(@errmsg, @errseverity, 1)
end catch
go


/***************************************** SurveySessions *****************************************/

go 
drop proc insertSurveySession
go
create proc insertSurveySession
@surveyId bigint,
@userId bigint
as 
begin
insert into SurveySessions(surveyId, userId) values(@surveyId, @userId)
select @@identity
end

go
drop proc doneSurveySession
go
create proc doneSurveySession
@id bigint
as 
update SurveySessions set done = 1 where @id = id

go
drop proc getSurveysForUser
go
create proc getSurveysForUser
@PageSize int,
@Page int,
@userId bigint
as
with result as (
        select q.id, q.surveyId, q.name, q.status,
                ROW_NUMBER() over(order by status desc) nr
        from (
select ss.id as id, ss.surveyId, s.name, 'undone' as status 
from SurveySessions ss inner join Surveys s on s.id = ss.surveyId
where ss.userId = @userId
and ss.done = 0
and s.active = 1
union
select 0 as id, s.id as surveyId, s.name, 'new' as status from surveys s 
where 
s.active = 1 and
s.id not in 
(select surveyId from surveysessions ss where ss.userid = @userId)
) as q )

select  id, surveyId, name, status
from    result
where   nr  between ((@Page - 1) * @PageSize + 1)
        and (@Page * @PageSize) 
        

go
drop proc getSurveysForUserCount
go
create proc getSurveysForUserCount
@userId bigint
as 

select count(*) from (
select ss.surveyId from SurveySessions ss where ss.userId = @userId and done = 0
union
select s.id as surveyId  from surveys s where s.id not in 
(select surveyId from surveysessions ss where ss.userId = @userId)
) as a

go
drop proc getSurveySession
go
create proc getSurveySession
@id bigint
as
select * from SurveySessions where id = @id

/***********************************   Answers   ***********************************/

go
drop proc insertAnswer
go
create proc insertAnswer
@sessionId bigint,
@questionId bigint,
@value nvarchar(255)
as
insert into answers(sessionId, questionId, value) values(@sessionId, @questionId, @value)

go
drop proc deleteAnswersBySessionId
go
create proc deleteAnswersBySessionId
@sessionId bigint
as
delete from answers where sessionId = @sessionId

go
drop proc getAnswersBySessionId
go
create proc getAnswersBySessionId
@id bigint
as
select * from answers where sessionId = @id

go
use master

--
--
--delete from questions where text is null

--select * from questions



